#!/usr/bin/env python
#coding=utf-8

#refer to:http://scienceoss.com/write-excel-files-with-python-using-xlwt/
#         and book "Working with Excel files in Python"
#               Chris Withers with help from John Machin
#               EuroPython 2009, Birmingham

import sys
import xlrd
import xlwt

#basic write operation
def basicwrite(filename):
    #initialize a Workbook object
    wbk = xlwt.Workbook()
    #add a Worksheet object
    sheet1 = wbk.add_sheet('sheet1')
    #write data
    icounter = 1
    for i in range(5):
        for j in range(5):
            sheet1.write(i, j, icounter)
            icounter += 1
    #save
    wbk.save(filename)    

#can not overwrite cell
def cannotoverwrite(filename):
    wbk=xlwt.Workbook()
    sheet2 = wbk.add_sheet('sheet2')
    sheet2.write(0,0,'some text')
    sheet2.write(0,0,'can not overwrite')
    wbk.save(filename)
    
#overwrite cell
def overwritecell(filename):
    wbk=xlwt.Workbook()
    sheet2 = wbk.add_sheet('sheet2',cell_overwrite_ok=True)
    sheet2.write(0,0,'some text')
    sheet2.write(0,0,'overwrite cell')
    wbk.save(filename)

#append data to sheet
def appenddatatosheet(filename):
    from xlutils.copy import copy
    
    rb=xlrd.open_workbook(filename, formatting_info = True)
    rs=rb.sheet_by_index(0)
    rowcount=rs.nrows
    colcount=rs.ncols
    wb=copy(rb)
    ws=wb.get_sheet(0)
    
    for i in range(2):
        for j in range(colcount):
            ws.write(rowcount+i,j,'test')
    
    wb.save(filename)

def main():
    testfilename='tmp.test.xls'
    if len(sys.argv)<2:
        print 'Usage: xlwt_example.py basic|notoverwrite|overwritecell|appenddata'
        return 1
    else:
        if sys.argv[1]=='basic':
            basicwrite(testfilename)
        elif sys.argv[1]=='notoverwrite':
            cannotoverwrite(testfilename)
        elif sys.argv[1]=='overwritecell':
            overwritecell(testfilename)
        elif sys.argv[1]=='appenddata':
            appenddatatosheet(testfilename)
        else:
            print 'unknown argument'
        return 0
    
if __name__ == '__main__':
    main()
